<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 
class General_Model extends Model 
{
    function General_Model()
    {
        parent::Model();
    }

// SELECT =================================

	function get_events($langcode){
		$sql="SELECT begin_date as e_date,address,id, name,title,content FROM events as e, events_description as d where e.id=d.events_id and language_code='".$langcode."' order by begin_date asc";
		$query = $this->db->query($sql);
		return $query->result_array();

	}
	function get_all_user($id,$thedate=0,$num, $offset)
	{
		if($id!='all')
		{
			$sql = "SELECT * FROM news where id <>".$id." and thedate <='".$thedate."' order by  thedate desc limit 8 ";
			$query = $this->db->query($sql);
			if ($query->num_rows() <= 0) 
			{
			$sql = "SELECT * FROM news where id <>".$id." and thedate >='".$thedate."' order by  thedate desc limit 8 ";
			$query = $this->db->query($sql);			
			}
		}
		else
		{
			$this->db->limit($num, $offset);
			$this->db->order_by("thedate", "desc"); 
			$query = $this->db->get('news');
		}
		return $query->result();
	}

	function get_user_detail($id)
	{

		$sql = "SELECT * FROM news where id=".$id;
		$query = $this->db->query($sql);
		
		$sqlpageview = "update news set pageview=pageview+1 where id=".$id;
		$this->db->query($sqlpageview);
		return $query->result();
	}
	function get_banners($langcode)
	{
		$this->db->select('*');
		$this->db->from('slideshow');
		$this->db->order_by("sorting", "desc");
		$this->db->where('language_code',$langcode);
		$query = $this->db->get();		
		return $query;
	} 
	function get_category($langcode)
	{
		$this->db->select('*');
		$this->db->from('category_description');
		$this->db->join('category', 'category.category_id = category_description.category_id');
		$this->db->order_by("category.category_id", "asc");
		$this->db->where('language_code',$langcode);
		$query = $this->db->get();		
		return $query;
	}
	function get_downloadlist($langcode,$category)
	{
		$this->db->select('*');
		$this->db->from('download_description');
		$this->db->join('download', 'download.id = download_description.download_id');
		$this->db->order_by("download.id", "desc");
		$this->db->where('category_id',$category);
		$this->db->where('language_code',$langcode);
		$query = $this->db->get();		
		return $query;
	} 
// INSERT =================================

	function insert_new_user($info)
	{
		$this->db->insert('ua_users', $info); 
	}
	
// UPDATE =================================

	function update_user($id)
	{

		$sql = "SELECT * FROM news where id=".$id;
		$query = $this->db->query($sql);
		
		$sqlpageview = "update news set pageview=pageview+1 where id=".$id;
		$this->db->query($sqlpageview);
		return $query->result();
	}
	
	function update_pageview($type,$alias)
	{
		if ($type=='product')
		{
			$sqlpageview = "update product set viewed=viewed + 1 where product_id IN (select product_id from product_description where namepro_alias='".$alias."')";	
		}
		else
		{
			$sqlpageview = "update article set pageview=pageview+1 where id IN (select article_id from article_description where title_alias='".$alias."')";	
		}
		$this->db->query($sqlpageview);
	}
	
// DELETE =================================

	function delete_user($id)
	{

		$sql = "SELECT * FROM news where id=".$id;
		$query = $this->db->query($sql);
		
		$sqlpageview = "update news set pageview=pageview+1 where id=".$id;
		$this->db->query($sqlpageview);
		return $query->result();
	}
	
// FUNCTION MANIPULATION	

	function check_user($name)
	{

		$sql = "SELECT * FROM news where id=".$id;
		$query = $this->db->query($sql);
		
		$sqlpageview = "update news set pageview=pageview+1 where id=".$id;
		$this->db->query($sqlpageview);
		return $query->result();
	}
	
	function get_support($langcode)
	{
		$this->db->select('*');
		$this->db->from('support_description');
		$this->db->join('support', 'support.id = support_description.support_id');
		$this->db->order_by("support.id", "asc");
		$this->db->where('language_code',$langcode);
		$query = $this->db->get();		
		return $query;
	}
	
	function get_friend_link()
	{
		$query = $this->db->get('friend_link');
		return $query;
	}
	function get_product_menu($langcode)
	{
		$this->db->select('*');
		$this->db->from('category_description');
		$this->db->join('category', 'category.category_id = category_description.category_id');
		$this->db->order_by("category.sort_order", "asc");
		$this->db->where('category_type_id', '1');
		$this->db->where('language_code',$langcode); 
		$query = $this->db->get();
		return $query;
	}	

	function get_sub_menu($opositelang,$current_menu)
	{		
		$sql="SELECT name_alias FROM category_description where language_id=".$opositelang." and category_id IN ( SELECT category_id FROM category_description WHERE name_alias='".$current_menu."' )";
		$query = $this->db->query($sql);
		return $query;
	}	

	function get_opositelang($opositelang,$current_menu)
	{		
		$sql="SELECT title_alias FROM article_description where language_code='".$opositelang."' and article_id IN ( SELECT article_id FROM article_description WHERE title_alias ='".$current_menu."' )";
		$query = $this->db->query($sql);
		return $query;
	}	
	
	function get_oposite_submenu($opositelang,$current_menu='',$detail_segment='')
	{
		if($detail_segment=='')
		{
		$sql="SELECT name_alias FROM category_description where language_code='".$opositelang."' and category_id IN ( SELECT category_id FROM category_description WHERE name_alias='".$current_menu."' )" ;
		}
		else
		{
		$sql="SELECT namepro_alias FROM product_description where language_code='".$opositelang."' and product_id IN ( SELECT product_id FROM product_description WHERE namepro_alias='".$detail_segment."' )" ;
		}
		$query = $this->db->query($sql);
		return $query;
	}
	
	function get_title_website($table,$field)
	{		
		$sql="SELECT name FROM ".$table." where ".$field." LIKE '".$current_menu."'";
		$query = $this->db->query($sql);
		return $query;
	}	
	function get_product($langcode,$current_menu=null)
	{
		if($current_menu==null)
		{
		$sql='SELECT product_description.namepro_alias,product_description.name,product_description.description, product.image,category_description.name_alias  as sub_title FROM category_description ,product,product_description,product_to_category WHERE  category_description.category_id=product_to_category.category_id and product_description.language_code ="'.$langcode.'" and category_description.language_code ="'.$langcode.'" and product.product_id =product_description.product_id and product.product_id=product_to_category.product_id';
		}
		else
		{
			$sql='SELECT product_description.namepro_alias,product_description.name,product_description.description, product.image,category_description.name_alias  as sub_title FROM category_description ,product,product_description,product_to_category WHERE  category_description.category_id=product_to_category.category_id and product_description.language_code ="'.$langcode.'" and category_description.language_code ="'.$langcode.'" and product.product_id =product_description.product_id and product.product_id=product_to_category.product_id and category_description.name_alias IN ( SELECT name_alias FROM category c, category_description d where c.parent_id IN (select m.category_id from category_description m where m.name_alias="'.$current_menu.'" ) and c.category_id=d.category_id and language_code="'.$langcode.'" ) ';
		}
		$query = $this->db->query($sql);
		return $query;
	}
	function get_search_product($langcode,$keyword)
	{
		$sql='SELECT product_description.namepro_alias,product_description.name,product_description.description, product.image,category_description.name_alias  as sub_title FROM category_description ,product,product_description,product_to_category WHERE product_description.name LIKE "%'.$keyword.'%"   and category_description.category_id=product_to_category.category_id and product_description.language_code ="'.$langcode.'" and category_description.language_code ="'.$langcode.'" and product.product_id =product_description.product_id and product.product_id=product_to_category.product_id';
		$query = $this->db->query($sql);
		return $query;
	}
	function get_product_sub_menu($langcode,$current_menu,$product_code='')
	{
		if ($product_code=='')
		{
			
			$sql='SELECT product_description.namepro_alias,product_description.name,product_description.description, product.image, category_description.name as sub_title,category_description.name_alias as menu_alias FROM category_description ,product,product_description,product_to_category WHERE category_description.category_id=product_to_category.category_id and category_description.name_alias="'.$current_menu.'" and product_description.language_code ="'.$langcode.'" and product.product_id =product_description.product_id and product.product_id=product_to_category.product_id';
		}
		else
		{
			$sql='SELECT * FROM product as P,product_description as D WHERE D.language_code ="'.$langcode.'" and D.namepro_alias="'.$product_code.'" and D.product_id=P.product_id';
		}
		//echo $sql;
		//exit;
		$query = $this->db->query($sql);
		return $query;


	}
	function get_menu_name($current_menu)
	{
	
		$query = $this->db->query('select name from category_description where name_alias="'.$current_menu.'"');

		if ($query->num_rows() > 0)
		{
		   $row = $query->row();
		   return $row->name;
		} 
	}
	function check_child_menu($current_menu,$langcode)
	{
		$sql="SELECT name_alias FROM `category` c, category_description d where c.parent_id IN (select m.category_id from category_description m where m.name_alias='".$current_menu."' ) and c.category_id=d.category_id and language_code='".$langcode."'";
		$query = $this->db->query($sql);
		return $query->num_rows();
	}
	function get_random_products($langcode)
	{
		
		/*$this->db->select('*');
		$this->db->from('product_description');
		$this->db->join('product', 'product.product_id = product_description.product_id');
		$this->db->order_by("product.sort_order", "asc");
		$this->db->limit(3);
		$this->db->where('new_product', 1);
		$this->db->where('language_code', $langcode); 
		$query = $this->db->get();*/
		
		$sql='SELECT product_description.namepro_alias,product_description.name,product_description.description, product.image,category_description.name_alias  as sub_title FROM category_description ,product,product_description,product_to_category WHERE new_product=1 AND category_description.category_id=product_to_category.category_id and product_description.language_code ="'.$langcode.'" and category_description.language_code ="'.$langcode.'" and product.product_id =product_description.product_id and product.product_id=product_to_category.product_id order by rand() limit 3';
		$query = $this->db->query($sql);
		return $query;
	}
	
	function get_listnews($langcode,$segment='',$category_id='',$num='', $offset='')
	{
		if (($segment=='') ||(is_numeric($segment)))
		{
			$this->db->limit($num, $offset);
		}
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		if (($segment!='') && (!is_numeric($segment)))
		{
			$this->db->like('title_alias', $segment);
		}
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('category_id', $category_id);
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;
	}
	function get_totalrow_listnews($langcode,$category_id)
	{
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('category_id', $category_id);
		$this->db->where('language_code', $langcode);  
		return $this->db->count_all_results();
	}
	function get_tabs($langcode,$tab)
	{
		
		$this->db->from('information_description');
		$this->db->join('information', 'information.information_id = information_description.information_id');
		$this->db->where('language_code', $langcode);
		if($tab=="publication")
		{
			$cond="information.information_id IN (1,2,3)";
			$this->db->where($cond);
		}
		if($tab=="about")
		{
			$cond="information.information_id IN (4,5,6,7,8,11)";
			$this->db->where($cond);
		}
		if($tab=="homemanager")
		{
			$cond="information.information_id =9";
			$this->db->where($cond);
		}
		if(($tab=='egitim') || ($tab=='education'))
		{
			$opp_segment='education';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		if(($tab=='yayinlar') || ($tab=='publications'))
		{
			$opp_segment='publications';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		if(($tab=='kadin')||($tab=='women'))
		{
			$opp_segment='women';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		if(($tab=='muzik')||($tab=='music'))
		{
			$opp_segment='music';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		if(($tab=='genclik')||($tab=='youth'))
		{
			$opp_segment='youth';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		if(($tab=='spor')||($tab=='sport'))
		{
			$opp_segment='sport';
			$cond="information.keyword ='".$opp_segment."'";
			$this->db->where($cond);
		}
		$query = $this->db->get();
		return $query;
	}
	function get_2latestnews($langcode)
	{
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->order_by("article.thedate", "desc");
		$this->db->limit(2);
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;
	}

    function get_total_news($langcode){
        $this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
        return $query;
    }
    
	function get_newsdetail($langcode,$id)
	{
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('language_code', $langcode);
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query;
	}
	function get_eventdetail($langcode,$id)
	{
		$this->db->from('events_description');
		$this->db->join('events', 'events.id = events_description.events_id');
		$this->db->where('language_code', $langcode);
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query;
	}
	function get_gallerydetail($id)
	{
		$this->db->from('gallery');
		$this->db->where('reference_id', $id);
		$query = $this->db->get();
		return $query;
	}
	function get_listsearch_result($langcode,$keyword)
	{
		
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->join('category_description', 'article.category_id = category_description.category_id');
		$this->db->join('category', 'category.category_id = category_description.category_id');
		$this->db->like('title', $keyword);
		$this->db->order_by("article.thedate", "desc");
		//$this->db->group_by(array("title", "article_description.language_code")); 
		
		$id = array('18');
		$this->db->where_not_in('article.id', $id);
		$this->db->where('article_description.language_code', $langcode); 
		$this->db->where('category_description.language_code', $langcode); 
		$query = $this->db->get();
		return $query;
	}

	function  get_staticpage($langcode,$id)
	{
		
		$this->db->select('description');
		$this->db->from('information_description');
		$this->db->join('information', 'information.information_id = information_description.information_id');
		$this->db->where('information.information_id', $id);
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;
	}
	function get_other_listnews($langcode,$urlid,$category_id='')
	{
		
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->not_like('title_alias', $urlid);
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('category_id', $category_id);
		$this->db->limit(10);
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;
	}
	
	function get_other_listproduct($langcode,$current_menu,$product_code)
	{
		
		$sql='SELECT * FROM product as P, product_description as D where D.namepro_alias <> "'.$product_code.'" and D.language_code="'.$langcode.'"  and P.product_id=D.product_id and P.product_id IN ( select PC.product_id from category_description as C,product_to_category as PC where C.name_alias="'.$current_menu.'" and PC.category_id=C.category_id)';
		
		$query = $this->db->query($sql);
		return $query;
	}
	
	function get_new_tips($langcode)
	{	
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->limit(5);
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('category_id', '9');
		$this->db->where('language_code', $langcode); 
		$query = $this->db->get();
		return $query;
	}	
	
	function get_foreword($langcode)
	{
		
		$this->db->select('*');
		$this->db->from('information_description');
		$this->db->where('information_id', '3');
		$this->db->where('language_code', $langcode); 
		$query = $this->db->get();
		return $query;
	}	
	function get_information_page($langcode,$id)
	{
		
		$this->db->select('*');
		$this->db->from('information_description');
		$this->db->join('information', 'information.information_id = information_description.information_id');
		$this->db->where('information.information_id', $id);
		$this->db->where('language_code', $langcode); 
		$query = $this->db->get();
		return $query;
	}	
	
	function get_hot_news($langcode)
	{
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->limit(2);

		$this->db->order_by("article.thedate", "desc");
		//$this->db->group_by(array("title", "article_description.language_code")); 
		
		$id = array('12');
		$this->db->where_in('article.category_id', $id);
		$this->db->where('article_description.language_code', $langcode); 
		$query = $this->db->get();
		return $query;

	}	
	function get_nutswiki($langcode)
	{
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->order_by("article.thedate", "desc");
		$this->db->where('category_id', 13);
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;

	}	
	function get_testimonial($langcode)
	{
		$this->db->select('*');
		$this->db->from('article_description');
		$this->db->join('article', 'article.id = article_description.article_id');
		$this->db->where('article.category_id =', 10);
		$this->db->order_by("article.id", "random");
		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;

	}	
	function get_testimonial_footer($langcode)
	{
		$this->db->select('*');
		$this->db->from('category_description');
		$this->db->join('category', 'category.category_id = category_description.category_id');
		$this->db->order_by("sort_order", "random");

		$this->db->where('language_code', $langcode);  
		$query = $this->db->get();
		return $query;

	}	

}
